﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Module;
using System.Data.SqlClient;
using Qmaker.DAL;
using System.Data;

namespace DLL
{
    public class DFamousRegion
    {
         
        #region SQL语句
        private const string SQL_INSERT = "insert into FamousRegion(FamousRegionId,Title,LastUpdate,DateCreated,Name,Address,Bak1,Bak2,Bak3,ImageSrc) values (@FamousRegionId,@Title,@LastUpdate,@DateCreated,@Name,@Address,@Bak1,@Bak2,@Bak3,@ImageSrc)";

        private const string SQL_UPDATE = "update FamousRegion set Title=@Title,LastUpdate=@LastUpdate,DateCreated=@DateCreated,Name=@Name,Address=@Address,Bak1=@Bak1,Bak2=@Bak2,Bak3=@Bak3,ImageSrc=@ImageSrc  where FamousRegionId=@FamousRegionId ";

        private const string SQL_SELECT_ID = "select FamousRegionId,Title,LastUpdate,DateCreated,Name,Address,Bak1,Bak2,Bak3,ImageSrc from FamousRegion  where FamousRegionId=@FamousRegionId";

        private const string SQL_SELECT_ALL = "select FamousRegionId,Title,LastUpdate,DateCreated,Name,Address,Bak1,Bak2,Bak3,ImageSrc from FamousRegion where Bak1 is null";

        private const string SQL_SELECT_BY_TITLE = "select FamousRegionId,Title,LastUpdate,DateCreated,Name,Address,Bak1,Bak2,Bak3,ImageSrc from FamousRegion where Title = @Title";
        
        #endregion

        #region 公共接口
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="userProfileInfo"></param>
        /// <returns></returns>
        public bool Insert(FamousRegionInfo famousRegionInfo)
        {
            if (famousRegionInfo == null)
                throw new ArgumentNullException("paperInfo");

            SqlParameter[] parms = GetInsertParms(famousRegionInfo);
            parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, SQL_INSERT, parms));
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="paperInfo"></param>
        /// <returns></returns>
        public bool Update(FamousRegionInfo famousRegionInfo)
        {
            if (famousRegionInfo == null || famousRegionInfo.FamousRegionId.ToString().Length != 18)
                throw new ArgumentNullException("famousRegionInfo");

            SqlParameter[] parms = GetInsertParms(famousRegionInfo);
            parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 根据Id获取名人专区
        /// </summary>
        /// <param name="famousRegionId"></param>
        /// <returns></returns>
        public FamousRegionInfo GetFamousRegionInfo(long famousRegionId)
        {
            FamousRegionInfo famousRegionInfo = new FamousRegionInfo();
            SqlParameter[] parms = {
                    new SqlParameter("@FamousRegionId",SqlDbType.BigInt)
                };
            parms[0].Value = famousRegionId;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ID, parms))
            {
                while (reader.Read())
                {
                    famousRegionInfo = GetInstanceByDataReader(reader);

                }
            }
            return famousRegionInfo;
        }

        /// <summary>
        /// 获取所有名人专区
        /// </summary>
        /// <returns></returns>
        public List<FamousRegionInfo> GetAllFamousRegions()
        {
            List<FamousRegionInfo> regions = new List<FamousRegionInfo>();

            FamousRegionInfo regionInfo = null;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ALL, null))
            {
                while (reader.Read())
                {
                    regionInfo = GetInstanceByDataReader(reader);
                    if (regionInfo != null && regionInfo.FamousRegionId != 0)
                        regions.Add(regionInfo);
                }
            }
            return regions;
        }
        public FamousRegionInfo GetFamousRegionInfoByTitle(string titleName)
        {
            FamousRegionInfo famousRegionInfo = new FamousRegionInfo();
            SqlParameter[] parms = {
                    new SqlParameter("@Title",SqlDbType.VarChar)
                };
            parms[0].Value = titleName;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_BY_TITLE, parms))
            {
                while (reader.Read())
                {
                    famousRegionInfo = GetInstanceByDataReader(reader);

                }
            }
            return famousRegionInfo;
        }
        #endregion


        #region 私有接口       

        private SqlParameter[] GetInsertParms(FamousRegionInfo famousRegionInfo)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@FamousRegionId", SqlDbType.BigInt,8),
					new SqlParameter("@Title", SqlDbType.NVarChar,20),
					new SqlParameter("@LastUpdate", SqlDbType.DateTime),
					new SqlParameter("@DateCreated", SqlDbType.DateTime),
                    new SqlParameter("@Name", SqlDbType.NVarChar,20),
                    new SqlParameter("@Address", SqlDbType.NVarChar,50),                    
                    new SqlParameter("@Bak1", SqlDbType.NVarChar,50),
                    new SqlParameter("@Bak2", SqlDbType.NVarChar,100),
                    new SqlParameter("@Bak3", SqlDbType.NVarChar,200),
                     new SqlParameter("@ImageSrc", SqlDbType.VarChar,50)                   };
            //Name,Address,Bak1,Bak2,Bak3
            return SetInsertParms(parameters, famousRegionInfo);
        }

        private SqlParameter[] SetInsertParms(SqlParameter[] parameters, FamousRegionInfo famousRegionInfo)
        {
            parameters[0].Value = famousRegionInfo.FamousRegionId;
            parameters[1].Value = famousRegionInfo.Title;
            parameters[2].Value = famousRegionInfo.LastUpdate;
            parameters[3].Value = famousRegionInfo.DateCreated;
            parameters[4].Value = famousRegionInfo.Name;
            parameters[5].Value = famousRegionInfo.Address;
            parameters[6].Value = famousRegionInfo.Bak1;
            parameters[7].Value = famousRegionInfo.Bak2;
            parameters[8].Value = famousRegionInfo.Bak3;
            parameters[9].Value = famousRegionInfo.ImageSrc;
            return parameters;
        }

        private FamousRegionInfo GetInstanceByDataReader(SqlDataReader reader)
        {
            FamousRegionInfo famousRegionInfo = new FamousRegionInfo();
                //FamousRegionId,Title,LastUpdate,DateCreated
            famousRegionInfo.FamousRegionId = reader.IsDBNull(0) ? 0 : reader.GetInt64(0);
            famousRegionInfo.Title = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
            famousRegionInfo.LastUpdate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
            famousRegionInfo.DateCreated = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
            famousRegionInfo.Name = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
            famousRegionInfo.Address = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
            famousRegionInfo.Bak1 = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
            famousRegionInfo.Bak2 = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
            famousRegionInfo.Bak3 = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
            famousRegionInfo.ImageSrc = reader.IsDBNull(9) ? string.Empty : reader.GetString(9);
            return famousRegionInfo;
        }

        /// <summary>
        /// 检查属性值是否为空
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        private SqlParameter[] SetInsertParmsCheck(SqlParameter[] param)
        {
            for (int i = 0; i < param.Length; i++)
            {
                if (param[i].Value == null)
                {
                    param[i].Value = DBNull.Value;                    
                }
               
            }
            return param;
        }
        #endregion

    }
}
